/*
 * GroupDao.java
 *
 */

package com.sun.demo.addressbook.db;
import com.sun.demo.addressbook.Group;
import com.sun.demo.addressbook.GroupListEntry;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Properties;

/**
 *
 */
public class GroupDao {
    private LppDbSetup lppDbSetup;
    private Connection dbConnection;
    private Properties dbProperties;
    private boolean isConnected;
    private String dbName;
    private PreparedStatement stmtCreateGroup;
    private PreparedStatement stmtUpdateGroup;
    private PreparedStatement stmtGetListEntries;
    private PreparedStatement stmtGetGroup;
    private PreparedStatement stmtDeleteGroup;
    private PreparedStatement stmtEmailListForGroup;
    
    public GroupDao() {
        lppDbSetup = LppDbSetup.getLppDbSetup();
        System.out.println(lppDbSetup.getDatabaseDiskLocation());
        System.out.println(lppDbSetup.getDatabaseUrl());
    }
    
    public boolean connect() {
        String dbUrl = lppDbSetup.getDatabaseUrl();
        try {
            dbConnection = DriverManager.getConnection(dbUrl, dbProperties);
            stmtCreateGroup = dbConnection.prepareStatement(strCreateGroup, Statement.RETURN_GENERATED_KEYS);
            stmtUpdateGroup = dbConnection.prepareStatement(strUpdateGroup);
            stmtGetGroup = dbConnection.prepareStatement(strGetGroup);
            stmtDeleteGroup = dbConnection.prepareStatement(strDeleteGroup);
            stmtEmailListForGroup = dbConnection.prepareStatement(strEmailListForGroup);
            
            isConnected = dbConnection != null;
        } catch (SQLException ex) {
            isConnected = false;
            ex.printStackTrace();
        }
        return isConnected;
    }
    
    public void disconnect() {
        if(isConnected) {
            String dbUrl = lppDbSetup.getDatabaseUrl();
            dbProperties.put("shutdown", "true");
            try {
                DriverManager.getConnection(dbUrl, dbProperties);
            } catch (SQLException ex) {
            }
            isConnected = false;
        }
    }
    
    public int createGroup(Group record) {
        int id = -1;
        try {
            stmtCreateGroup.clearParameters();
            
            stmtCreateGroup.setString(1, record.getGroupName());
            stmtCreateGroup.setString(2, record.getNotes());
            int rowCount = stmtCreateGroup.executeUpdate();
            ResultSet results = stmtCreateGroup.getGeneratedKeys();
            if (results.next()) {
                id = results.getInt(1);
            }
        } catch(SQLException sqle) {
            sqle.printStackTrace();
        }
        return id;
    }
    
    public boolean editGroup(Group record) {
        boolean bEdited = false;
        try {
            stmtUpdateGroup.clearParameters();
            stmtUpdateGroup.setString(1, record.getGroupName());
            stmtUpdateGroup.setString(2, record.getNotes());
            stmtUpdateGroup.setInt(3, record.getId());
            stmtUpdateGroup.executeUpdate();
            bEdited = true;
        } catch(SQLException sqle) {
            sqle.printStackTrace();
        }
        return bEdited;
    }
    
    public boolean deleteGroup(int id) {
        boolean bDeleted = false;
        try {
            stmtDeleteGroup.clearParameters();
            stmtDeleteGroup.setInt(1, id);
            stmtDeleteGroup.executeUpdate();
            bDeleted = true;
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return bDeleted;
    }
    
    public boolean deleteGroup(Group record) {
        int id = record.getId();
        return deleteGroup(id);
    }
    
    public List<GroupListEntry> getListEntries() {
        List<GroupListEntry> listEntries = new ArrayList<GroupListEntry>();
        Statement queryStatement = null;
        ResultSet results = null;
        
        try {
            queryStatement = dbConnection.createStatement();
            results = queryStatement.executeQuery(strGetGroupListEntries);
            while(results.next()) {
                int id = results.getInt(1);
                String groupName = results.getString(2);
                GroupListEntry entry = new GroupListEntry(groupName, id);
                listEntries.add(entry);
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return listEntries;
    }
    
    public Group getGroup(int index) {
        Group group = null;
        try {
            stmtGetGroup.clearParameters();
            stmtGetGroup.setInt(1, index);
            ResultSet result = stmtGetGroup.executeQuery();
            if (result.next()) {
                String groupName = result.getString("GROUPNAME");
                String notes = result.getString("NOTES");
                int id = result.getInt("ID");
                group = new Group(groupName, notes, id);
            }
        } catch(SQLException sqle) {
            sqle.printStackTrace();
        }
        return group;
    }
    
    public HashSet<String> getEmailListForGroup(int groupId) {
        HashSet<String> emailAddresses = new HashSet<String>();
        try {
            stmtEmailListForGroup.clearParameters();
            stmtEmailListForGroup.setInt(1, groupId);
            ResultSet results = stmtEmailListForGroup.executeQuery();
            while(results.next()) {
                String emailAddress = results.getString(1);
                emailAddresses.add(emailAddress);
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return emailAddresses;
    }

    private static final String strGetGroup =
            "SELECT * FROM APP.GROUPDATA " +
            "WHERE ID = ?";
    
    private static final String strCreateGroup =
            "INSERT INTO APP.GROUPDATA " +
            "   (GROUPNAME, NOTES) " +
            "VALUES (?, ?)";
    
    private static final String strGetGroupListEntries =
            "SELECT ID, GROUPNAME FROM APP.GROUPDATA "  +
            "ORDER BY GROUPNAME ASC";
    
    private static final String strUpdateGroup =
            "UPDATE APP.GROUPDATA " +
            "SET GROUPNAME = ?, " +
            "    NOTES = ? " +
            "WHERE ID = ?";
    
    private static final String strDeleteGroup =
            "DELETE FROM APP.GROUPDATA " +
            "WHERE ID = ?";

    private static final String strEmailListForGroup =
            "select EMAIL " +
            "from APP.ADDRESS ADDRESS " +
            "INNER JOIN APP.PART ON PART.ID = APP.ADDRESS.ASSIGNEDPART " +
            "where ADDRESS.ASSIGNEDPART IN " +
            "(select PARTID " +
            "from APP.PARTGROUP " +
            "where APP.PARTGROUP.GROUPID = ?)";

    public static void main(String[] args) {
        GroupDao db = new GroupDao();
        db.connect();
        db.disconnect();
    }
}
